package com.xiyou.io.week05.hikari;

import com.xiyou.io.week05.dao.model.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * hikari连接
 */
public class HikariTest {

    private static final String driverClassName = "com.mysql.jdbc.Driver";    //启动驱动
    private static final String url = "jdbc:mysql://192.168.0.54:3307/user";    //设置连接路径
    private static final String username = "dev";    //数据库用户名
    private static final String password = "123456";    //数据库连接密码

    public static void main(String[] args) {
        /**
         * 新增
         */
//        User user = new User();
//        user.setName("张三");
//        user.setPhone("18888888888");
//        add(user);

        /**
         * 批量新增
         */
//        List<User> userList = new ArrayList<>();
//        user = new User();
//        user.setName("李四");
//        user.setPhone("17777777777");
//        userList.add(user);
//
//        user = new User();
//        user.setName("王五");
//        user.setPhone("16666666666");
//        userList.add(user);
//
//        batchAdd(userList);

        /**
         * 查询
         */
//        getUserInfo(user.getPhone());

        /**
         * 修改
         */
//        user.setName("张三222");
//        edit(user);

        /**
         * 批量修改
         */
        List<User> userList = new ArrayList<>();
        User user = new User();
        user.setName("李四2");
        user.setPhone("17777777777");
        userList.add(user);

        user = new User();
        user.setName("王五2");
        user.setPhone("16666666666");
        userList.add(user);

//        batchEdit(userList);

        /**
         * 查询
         */
//        getUserInfo(user.getPhone());

        /**
         * 删除
         */
//        delete(user.getPhone());

        /**
         * 批量删除
         */
        List<String> phoneList = new ArrayList<>();
        phoneList.add("17777777777");
        phoneList.add("16666666666");
        batchDelete(phoneList);
    }

    private static void add(User user) {
        System.out.println("-------------开始新增------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接


            String sql = "INSERT INTO user (name,phone) VALUES(?,?)"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getPhone());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void batchAdd(List<User> userList) {
        System.out.println("-------------开始批量新增------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接

            con.setAutoCommit(false); //设置手动提交

            String sql = "INSERT INTO user (name,phone) VALUES(?,?)"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);
            for (User user : userList) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getPhone());
                // 添加到批次
                pstmt.addBatch();
            }
            pstmt.executeBatch();//提交批处理
            con.commit();//执行
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void edit(User user) {
        System.out.println("-------------开始修改------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接


            String sql = "update user set name = ? where phone =?"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getPhone());
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void batchEdit(List<User> userList) {
        System.out.println("-------------开始批量修改------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接

            con.setAutoCommit(false); //设置手动提交

            String sql = "update user set name = ? where phone =?"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);
            for (User user : userList) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getPhone());
                pstmt.addBatch();
            }
            pstmt.executeBatch();//提交批处理
            con.commit();//执行
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void delete(String phone) {
        System.out.println("-------------开始删除------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接


            String sql = "delete from user where phone =?"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, phone);
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void batchDelete(List<String> phoneList) {
        System.out.println("-------------开始批量删除------------");
        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接

            con.setAutoCommit(false); //设置手动提交

            String sql = "delete from user where phone =?"; //设置的预编译语句格式
            pstmt = con.prepareStatement(sql);

            for (String phone : phoneList) {
                pstmt.setString(1, phone);
                pstmt.addBatch();
            }
            pstmt.executeBatch();//提交批处理
            con.commit();//执行
            pstmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }

    private static void getUserInfo(String phone) {
        System.out.println("-------------开始查询------------");

        Connection con = null;        //连接
        PreparedStatement pstmt = null;    //使用预编译语句
        ResultSet rs = null;    //获取的结果集
        try {
            Class.forName(driverClassName); //执行驱动
            con = DriverManager.getConnection(url, username, password); //获取连接


            String sql = "SELECT * FROM user WHERE phone = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setNString(1, phone);

            rs = pstmt.executeQuery();
            if (rs != null) {
                if (rs.next()) {
                    System.out.println(rs.getString("id"));
                    System.out.println(rs.getString("name"));
                    System.out.println(rs.getString("phone"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            //关闭资源,倒关
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();  //必须要关
            } catch (Exception e) {
            }
        }
    }
}
